package com.example.caorl.jdbc.dao.impl;


import com.example.caorl.jdbc.dao.IStudentDAO;
import com.example.caorl.jdbc.domain.Student;
import com.example.caorl.jdbc.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class StudentDAOImpl implements IStudentDAO {

    public void save(Student stu) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "INSERT INTO student VALUES(?,?,?)";
        try {
            conn = JDBCUtil.getConn();
            // 关闭自动提交:
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            //这里的索引从1开始，而不是0
            ps.setLong(1, stu.getId());
            ps.setString(2, stu.getName());
            ps.setInt(3, stu.getAge());
            ps.executeUpdate();//无参数
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollback(conn);
        } finally {
            JDBCUtil.reset(conn);
        }
        JDBCUtil.close(conn, ps, null);
    }

    public void delete(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "DELETE FROM student WHERE id=?";
        try {
            conn = JDBCUtil.getConn();
            // 关闭自动提交:
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ps.executeUpdate();
            // 提交事务:
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollback(conn);
        } finally {
            JDBCUtil.reset(conn);
        }
        JDBCUtil.close(conn, ps, null);
    }

    public void update(Student stu) {
        Connection conn = null;
        PreparedStatement ps = null;
        //String sql="UPDATE student SET(id,name,age) VALUES(?,?,?)";
        String sql = "UPDATE student SET name=?,age=? WHERE id=?";
        try {
            conn = JDBCUtil.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1, stu.getName());
            ps.setInt(2, stu.getAge());
            ps.setLong(3, stu.getId());
            System.out.println(sql);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtil.close(conn, ps, null);

    }

    public Student get(Long id) {
        Student stu = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM student WHERE id=?";
        try {
            conn = JDBCUtil.getConn();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            //这里是无参的
            rs = ps.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                stu = new Student(id, name, age);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtil.close(conn, ps, rs);
        return stu;
    }

    public List<Student> list() {
        List<Student> stus = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM student";
        try {
            conn = JDBCUtil.getConn();
            ps = conn.prepareStatement(sql);
            //executeUpdate()返回的是受影响行数
            //executeQuery()返回的是结果集，用ResultSet接收
            rs = ps.executeQuery();
            while (rs.next()) {
                Long id = rs.getLong("id");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Student stu = new Student(id, name, age);
                stus.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtil.close(conn, ps, rs);
        return stus;
    }

}
